﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.Collections;

public partial class XSSJ_XSSJList : System.Web.UI.Page
{
  protected void Page_Load(object sender, EventArgs e)
  {
    if (!Page.IsPostBack)
    {
      if (!ProjectPrivate.GetQX("49"))
      {
        Server.Transfer("~\\blank.aspx");
      }
      //if (CWSD.GetSDZT("否"))
      //{
      //  Server.Transfer("~\\sd.aspx");
      //}
      date1.Date = CommonUse.GetYC();
      date2.Date = CommonUse.GetYM();
    }
    BindGrid();
  }

  protected void BindGrid()
  {
    StringBuilder sbSql = new StringBuilder();
    sbSql.Append(@"select  ROW_NUMBER() OVER (ORDER BY a.cbsjid desc) AS xh,a.*,convert(varchar,a.CBSJ,23) BYCBSJ,convert(varchar,a.scCBSJ,23) SYCBSJ,a.scBS SYBS,
c.KHMC,D.KHJB,E.DWMC,C.IC,c.KH,c.XXDZ,c.LXDH,f.CBR,isnull(i.Bqfy,0) JE FROM 
    (select * from sjlr_jgtz_cbsj where cbsj>=@0 and cbsj<=cast(@1 as datetime)) A 
    left join jcxx_khxx c on a.khid=c.khid 
    left join jcxx_khjb d on c.khjbid=d.khjbid  
    left join jcxx_dw e on c.dwid=e.dwid
left join jcxx_cbr f on a.cbrid=f.cbrid
left join (select a.cbsjid,sum(ysfy) BQFY FROM sjlr_jgtz_cbsj A join sjlr_jgtz_bqql B on a.cbsjid=b.cbsjid group by a.cbsjid) i
  on a.cbsjid=i.cbsjid 
WHERE a.dlm=@2 

");
    //sbSql.Append("order by a.cbsjid desc");
    SqlParameter[] cmdParams = { new SqlParameter("@0", SqlDbType.VarChar, 10), new SqlParameter("@1", SqlDbType.VarChar, 10),
                                  new SqlParameter("@2", SqlDbType.VarChar, 10)};
    cmdParams[0].Value = date1.Text;
    cmdParams[1].Value = date2.Text;
    cmdParams[2].Value = SysManage.Get_User;

    DataSet ds = DbHelperSQL.Query(sbSql.ToString(), cmdParams);
    string sSql = @"select a.*,b.yqxz from sjlr_jgtz_bqql a,jcxx_yqxz b, sjlr_jgtz_cbsj c 
where a.yqxzid=b.yqxzid and a.cbsjid=c.cbsjid and cbsj>=@0 and cbsj<=@1 and c.dlm=@2 ";
    cmdParams = new SqlParameter[3]{ new SqlParameter("@0", SqlDbType.VarChar, 10), new SqlParameter("@1", SqlDbType.VarChar, 10),
                                  new SqlParameter("@2", SqlDbType.VarChar, 10)};
    cmdParams[0].Value = date1.Text;
    cmdParams[1].Value = date2.Text;
    cmdParams[2].Value = SysManage.Get_User;
    SqlDataReader dr = DbHelperSQL.ExecuteReader(sSql, cmdParams);
    Hashtable ht = new Hashtable();
    while (dr.Read())
    {
      ht.Add(dr["cbsjid"].ToString() + "," + dr["yqxzid"].ToString(),
        dr["bqql"].ToString() + "," + dr["yqxz"].ToString() + "," + dr["bqql_sfsb"].ToString());
    }
    dr.Close();
    MergeDS(ds.Tables[0], ht);
    //MergeDS(ds.Tables[0], dsQLMX.Tables[0]);
    ASPxGridView1.DataSource = ds;
    ASPxGridView1.DataBind();
  }

  protected void MergeDS(DataTable dt, Hashtable ht)
  {
    dt.Columns.Add("QLMX");
    dt.Columns.Add("BQQL_SFSB");
    string sQLMX = "";
    string sSFSB = "0";
    string sCBSJID = "";
    for (int i = 0; i < dt.Rows.Count; i++)
    {
      sCBSJID = dt.Rows[i]["cbsjid"].ToString();
      bool flag = false;
      foreach (DictionaryEntry de in ht)
      {
        string[] key = de.Key.ToString().Split(new char[] { ',' });
        string[] value = de.Value.ToString().Split(new char[] { ',' });
        if (key[0] == sCBSJID)
        {
          sQLMX = sQLMX + value[1] + ":" + value[0] + ",";
          if (!flag)
          {
            if (value[2] != "0")
            {
              sSFSB = value[2];
              flag = true;
            }
          }
        }
      }
      sQLMX = sQLMX.Trim(new char[] { ',' });
      dt.Rows[i]["QLMX"] = sQLMX;
      sQLMX = "";
      dt.Rows[i]["BQQL_SFSB"] = sSFSB;
      sSFSB = "0";
    }
  }

  protected void MergeDS(DataTable dt, DataTable dtQLMX)
  {
    dt.Columns.Add("QLMX");
    dt.Columns.Add("BQQL_SFSB");
    string sQLMX = "";
    string sSFSB = "0";
    for (int i = 0; i < dt.Rows.Count; i++)
    {
      bool flag = false;
      for (int j = 0; j < dtQLMX.Rows.Count; j++)
      {
        if (dt.Rows[i]["cbsjid"].ToString() == dtQLMX.Rows[j]["cbsjid"].ToString())
        {
          sQLMX = sQLMX + dtQLMX.Rows[j]["yqxz"].ToString() + ":" + dtQLMX.Rows[j]["bqql"].ToString() + ",";
          if (!flag)
          {
            if (dtQLMX.Rows[j]["bqql_sfsb"].ToString() != "0")
            {
              sSFSB = dtQLMX.Rows[j]["bqql_sfsb"].ToString();
              flag = true;
            }
          }
        }
      }
      sQLMX = sQLMX.Trim(new char[] { ',' });
      dt.Rows[i]["QLMX"] = sQLMX;
      sQLMX = "";
      dt.Rows[i]["BQQL_SFSB"] = sSFSB;
      sSFSB = "0";
    }
  }

  protected void lbDelete_Command(object sender, CommandEventArgs e)
  {
    string sKHID = "", sRQ = "", bybs = "";
    SqlDataReader dr = CBSJ.GetCBSJ(e.CommandArgument.ToString());
    if (dr.Read())
    {
      sKHID = dr["khid"].ToString();
      sRQ = dr["rq"].ToString();
      bybs = dr["bybs"].ToString();
    }
    dr.Close();

    //if (CBSJ.ExistHQID(sKHID, sRQ))
    //{
    //  ClientScriptManager cs = Page.ClientScript;
    //  cs.RegisterStartupScript(typeof(string), "TabJs", "<script language='javascript'>alert( '本次日期之后已有数据，不能修改！'); </script>");
    //  return;
    //}
    List<CommandInfo> cis = new List<CommandInfo>();
    string cbsjid = e.CommandArgument.ToString();
    cis.Add(CBSJ.Delete(cbsjid));
    cis.Add(BQQL.Delete(cbsjid));
    cis.Add(JGTZ_CBSJ.Delete(cbsjid));
    cis.Add(JGTZ_BQQL.Delete(cbsjid));
    //cis.Add(CommonUse.DeleteCI("zhcx", "cx_id", e.CommandArgument.ToString()));

    if (DbHelperSQL.ExecuteSqlTran(cis) > 0)
    {
      string nr = "删除抄表数据:客户编码为" + sKHID + " 日期为" + sRQ + " 本月表数为" + bybs;
      rzcz.InsertRZ(nr);
      //ZHCX.CreateZHCX(sKHID, Convert.ToDateTime(sRQ).ToString("yyyy-MM-dd"));
      //ZHCX.UpdateCB(sKHID, Convert.ToDateTime(sRQ).ToString("yyyy-MM-dd"), e.CommandArgument.ToString());
      BindGrid();
    }
  }

  protected void lbSB_Command(object sender, CommandEventArgs e)
  {
    SqlParameter[] cmdParams = null;
    string sSql = "";
    List<CommandInfo> cis = new List<CommandInfo>();
    CommandInfo ci;

    sSql = "update SJLR_bqql set bqql_SFSB=1 where cbsjid=" + e.CommandArgument.ToString();
    ci = new CommandInfo();
    ci.CommandText = sSql;
    ci.Parameters = cmdParams;
    cis.Add(ci);

    sSql = "update SJLR_jgtz_bqql set bqql_SFSB=1 where cbsjid=" + e.CommandArgument.ToString();
    ci = new CommandInfo();
    ci.CommandText = sSql;
    ci.Parameters = cmdParams;
    cis.Add(ci);

    if (DbHelperSQL.ExecuteSqlTran(cis) > 0)
    {
      BindGrid();
    }
  }

  protected void btnQuery_Click(object sender, EventArgs e)
  {

  }

  protected void btnExport_Click(object sender, EventArgs e)
  {
    this.ASPxGridViewExporter1.WriteXlsToResponse();
  }

  protected void cpPopup_Callback(object source, DevExpress.Web.ASPxClasses.CallbackEventArgsBase e)
  {
    litText.Text = CBSJ.GetNotes(e.Parameter).ToString();
  }

}
